package sg.edu.nus.dashboard.service.sqlexecutor;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import sg.edu.nus.dashboard.service.dbconn.ConnectionService;

public class SQLService {
	
	public List exe(String sql) {
		List resultList = null;
		try {
			ConnectionService cs = new ConnectionService();
			Connection conn = cs.getConnection();

			if (conn != null) {
				Statement st = conn.createStatement();
				ResultSet rs = st.executeQuery(sql);

				// handle execution result
				System.out.println(rs.toString());
				if(rs.getMetaData()!=null){
					//System.out.println("checking metadata");
					if (rs.getMetaData().getColumnCount()>0) {
						resultList = new ArrayList<Object>();
						int columnCount = rs.getMetaData().getColumnCount();
						List<String> columnNames = new ArrayList<String>(columnCount);
						for (int i=1; i<=columnCount; i++) {
							columnNames.add(rs.getMetaData().getColumnName(i));
							//System.out.println(i+" : "+columnNames.get(i-1));
						}
						resultList.add(columnNames);
						while(rs.next()) {
							List<Object> rowDatas = new ArrayList<Object>(columnCount);
							for (int i=1; i<=columnCount; i++) {
								rowDatas.add(rs.getObject(i));
							}
							resultList.add(rowDatas);
						}
						System.out.println("ResultList: "+resultList.size());
					}
				}

				rs.close();
				st.close();
			}
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
			resultList = this.getExceptionMessage(e);
		}
		return resultList;
	}
	
	public List execute(String sql) {
		List resultList = null;
		try {
			ConnectionService cs = new ConnectionService();
			Connection conn = cs.getConnection();

			if (conn != null) {
				Statement st = conn.createStatement();
				ResultSet rs = st.executeQuery(sql);

				// handle execution result
				System.out.println(rs.toString());
				if(rs.getMetaData()!=null){
					//System.out.println("checking metadata");
					if (rs.getMetaData().getColumnCount()>0) {
						resultList = new Vector();
						int columnCount = rs.getMetaData().getColumnCount();
						Vector<String> columnNames = new Vector<String>(columnCount);
						for (int i=1; i<=columnCount; i++) {
							columnNames.add(rs.getMetaData().getColumnName(i));
							//System.out.println(i+" : "+columnNames.get(i-1));
						}
						resultList.add(columnNames);
						while(rs.next()) {
							Vector<Object> rowDatas = new Vector<Object>(columnCount);
							for (int i=1; i<=columnCount; i++) {
								rowDatas.add(rs.getObject(i));
							}
							resultList.add(rowDatas);
						}
						//System.out.println("ResultList: "+resultList);
					}
				}

				rs.close();
				st.close();
			}
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
			resultList = this.getExceptionMessage(e);
		}
		return resultList;
	}

	public boolean executeUpdate(String sql) {
		boolean result = false;
		try {
			ConnectionService cs = new ConnectionService();
			Connection conn = cs.getConnection();

			if (conn != null) {
				Statement st = conn.createStatement();
				int rs = st.executeUpdate(sql);
				//result = (rs==1) ;
				result = true;
				System.out.println(rs +" row(s) updated/inserted.");
				st.close();
			}
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private List getExceptionMessage(Exception e) {
		String errorMessage = null;
		errorMessage = e.getMessage();
		//To add on if errorMessage is empty
		List resultList = new ArrayList<Object>();
		List<String> columnNames = new ArrayList<String>();
		columnNames.add(errorMessage);
		resultList.add(columnNames);
		return resultList;
	}
}
